<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2017 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://think.ctolog.com
// +----------------------------------------------------------------------
// | 开源协议 ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | github开源项目：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace service;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Exception;

/**
 *
 * Class ExcelService
 * @author SuperX <xieshouchao@cuci.cc>
 * @package service
 */
class ExcelService
{

    protected static $allow_format = ['xls', 'xlsx', 'csv', 'txt'];

    /**
     * @param string $file 文件路径
     * @param int $rowNum 指定读取数据行数
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function import($file = '', $rowNum = 1)
    {
        // 文件后缀
        $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        if (!in_array($extension, self::$allow_format)) throw new \Exception("不支持{$extension}格式文件导入");
//        $file = iconv("utf-8", "gb2312", $path);

        if (empty($file) OR !file_exists($file)) {
            throw new \Exception('文件不存在!');
        }

        $data = $extension != 'txt' ? self::importExcel($file, $extension, $rowNum) : self::importTxt($file, $rowNum);
        unlink($file);
        return $data;
    }

    /**
     * 导入Excel
     * @param string $file
     * @param string $extension
     * @param int $rowNum
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    protected static function importExcel($file, $extension, $rowNum)
    {

        $objRead = IOFactory::createReader(ucfirst($extension));

        if (!$objRead->canRead($file)) {
            throw new \Exception('无法读取文件！');
        }

        $sheet = 0;

        /* 建立excel对象 */
        $obj = $objRead->load($file);
        /* 获取指定的sheet表 */
        $currSheet = $obj->getSheet($sheet);

        /* 取得最大的列号 */
        $columnH = $currSheet->getHighestColumn();
        /* 兼容原逻辑，循环时使用的是小于等于 */
        $columnCnt = Coordinate::columnIndexFromString($columnH);

        /* 获取总行数 */
        $rowCnt = $currSheet->getHighestRow();
        $data = [];

        /* 读取内容 */
        for ($_row = $rowNum; $_row <= $rowCnt; $_row++) {
            $isNull = true;

            for ($_column = 1; $_column <= $columnCnt; $_column++) {
                $cellName = Coordinate::stringFromColumnIndex($_column);
                $cellId = $cellName . $_row;
                $cell = $currSheet->getCell($cellId);

                if (isset($options['format'])) {
                    /* 获取格式 */
                    $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                    /* 记录格式 */
                    $options['format'][$_row][$cellName] = $format;
                }

                if (isset($options['formula'])) {
                    /* 获取公式，公式均为=号开头数据 */
                    $formula = $currSheet->getCell($cellId)->getValue();

                    if (0 === strpos($formula, '=')) {
                        $options['formula'][$cellName . $_row] = $formula;
                    }
                }

                if (isset($format) && 'm/d/yyyy' == $format) {
                    /* 日期格式翻转处理 */
                    $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                }

                $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                if (!empty($data[$_row][$cellName])) {
                    $isNull = false;
                }
            }

            /* 判断是否整行数据为空，是的话删除该行数据 */
            if ($isNull) {
                unset($data[$_row]);
            }
        }
        return $data;
    }

    /**
     * 导入txt
     * @param string $file 文件路径
     * @param int $rowNum 指定读取数据行数
     * @return array
     */
    protected static function importTxt($file, $rowNum)
    {
        $data = [];
        if (file_exists($file)) {
            $fp = fopen($file, "r");
            while (!feof($fp)) {
                $row = explode('	', fgets($fp));
                if (!empty($row[0])) {
                    $data[] = $row;
                }
            }
        }

        if ($rowNum > 1) {
            if ($rowNum > count($data)) {
                $data = [];
            } else {
                for ($i = 0; $i < $rowNum - 1; $i++) {
                    unset($data[$i]);
                }
            }
        }
        return $data;
    }


    /**
     * 导出
     */
    public static function export($field, array $data, $title, $format = 'xls')
    {
        if (!in_array($format, self::$allow_format)) {
            throw new Exception("不支持{$format}格式导出！");
        }

        $colRange = self::getColRange();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $keys = array_keys($field);
        foreach (array_merge([$field], $data) as $row => $vo) {
            foreach ($keys as $kd => $td) {
                $sheet->setCellValue($colRange[$kd] . ($row + 1), $vo[$td]);
            }
        }

//        $sheet->freezePane();
//        $sheet->getStyle()
//        $sheet->freezePaneByColumnAndRow(1, 2);
//        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename={$title}.{$format}");
        header('Cache-Control: max-age=0');
        ob_clean();
        ob_start();
        $writer = IOFactory::createWriter($spreadsheet, ucfirst($format));
        $writer->save('php://output');
        /* 释放内存 */
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
    }

    /**
     * 获取列范围
     * @return array
     */
    protected static function getColRange()
    {
        return ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];
    }


}
